Modifying and creating SSRS reports

This report is only using an IQA query to generate its results. You can modify the IQA in iMIS to change the results of the report. For more information, see Reporting in IQA.

IQA based reports use the Query Menu content item.

IQA reports:

• IQA is familiar to many users and no knowledge of SQL is required.
• Using IQA enforces use of the best practice of using business objects as data sources rather than SQL tables.
• IQA allows you to incorporate dynamic filters such as @LoggedInUser and @SelectedUser to display report data based on who is logged in or which user record is selected.
• IQA based reports usually require more planning upfront as it can be time consuming to modify if you forgot a field in the original query or need to make changes to the data source once the report layout exists.
• More maintenance is required as every report you create needs both the report template and the query.

iMIS comes with many built in IQA queries that you can use as a basis for reports. The Report tab in the Intelligent Query Architect window generates the report that you can then save, export, or print.

In these reports, IQA is used to define and pull the data, while the SSRS report is used to define the layout of the report. For more information, see Modifying an existing report and Creating IQA-SSRS reports.

These are the most commonly used types of reports. Combination IQA-SSRS reports use the SSRS Report content item.

IQA-SSRS reports:

• You have complete control over the datasets you create and can go directly into the SQL code to modify the report.
• Changes to the report can be made directly, with fields easily added and removed without needed to go back and forth to iMIS.
• There are fewer steps and you can get reports to your site quickly.
• You cannot use dynamic filters.
• Some SQL knowledge is required.
• There is no control over using tables, views, or business objects. This means people can write reports referencing tables or views that might be overwritten with an upgrade or other user changes. The best practice is to use Business Objects as the source for reports.

These reports do not rely on an IQA query to generate results, although they can be generated using IQA. These reports are files with an RDL extension, and are called Reporting Services Templates in iMIS.

Standalone SSRS reports allow for more creative freedom, but should be reserved for more experienced reporting users. If you have designed your own RDL templates outside of iMIS, you can upload them into iMIS templates. This method permits you to use multiple data sets. For more information, see Creating standalone SSRS reports.

These type of reports can be used as attachments in communication templates, or in the Run a report action of an automated task. For more information, see Creating a new communication template, and Creating a scheduled report task.

You can display those reports using the Report Display content item.

Warning! The Report Display content item is designed to be on a page by itself. Adding the Report Display content item to a page with other content items will cause the page to break. If you need to display a report on a page with other items, use the SSRS Report content item.

Can't I just use IQA?

Yes, take advantage of queries and special content items to filter and present iMIS data to your users. See Configuring content items for the growing number of query-based content items available.

But some situations require the full power of Reporting Services. This includes expanded options to:

• Display a different type of chart or graph than is available
• Change the chart from listing counts to showing percentages
• Add special header/footer and page layout for production quality
• Output the records in list format, one per page

How hard is adding a report?

The process of publishing a new report in iMIS is quite simple. Suppose that you need a new report, and you want this report added to your site for easy access. Without using any report editors, you can achieve this in a few steps:

1. Create and save your query.
2. Click Report to generate the default report.

Note: If you want to adjust any layout defaults, you can do so in SystemConfig table settings. See Changing the default layout for SSRS reports.

3. Click Save Report Definition to iMIS. Select the name and location you want and click Save, which creates the template and report file, already bound to your query.
4. Add access to your new report by adding an SSRS Report content item (Utility folder) to an appropriate content record. Enable Excel and PDF exporting as needed.

Note: Most reports display best on content records that use single-column layouts.

What is the easiest way to change a report?

Suppose that you already added a default report to your site, but you now need to add a pie chart. The essential process is this:

1. Download the RDL template: edit the query, click Report, click Save Report Definition to Workstation, and Save it.
2. Edit it in your report writer.
• Tutorial: Adding a Pie Chart to a Report
• How to: Display Data Point Labels Outside a Pie Chart
3. Upload it to iMIS as a new template: in the Document browser, select New > Reporting Services Template, and browse to your RDL file.
4. Update the report runtime to use it: edit the report file, and browse to your new template.

See Modifying an existing report.

How do I attach report results to an email?

To generate a standalone SSRS report using an IQA query:

1. Create an IQA query that does not use any optional filter parameters.
2. Click Report to generate a report that is dynamically formatted using the settings you control through SystemConfig parameters.
3. Under Options, select Save Report Definition to iMIS. Select the name and location you want and click Save, which creates the template and report file . The template (Reporting Services Template) document is the one that can be used as an attachment in communications.

You can then attach the Reporting Services Template file that you generated to a communication template. See Creating a new communication template for more information. When the communication is sent, the report is run and the results are attached as a PDF file to the email.

See Generating a standalone SSRS report using IQA.